package Dao;

import entity.CourtsBean;
import entity.CourtsTypeBean;
import entity.Roles_PerBean;
import util.JDBCUtil;
import util.JdbcUtil2;

import java.sql.*;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class CourtsDao {
    private static CourtsDao courtsDao;
    private static final List<CourtsTypeBean> courtsTypeBeanList = getCourtsTypeAll();

    /**
     * 获取所有场地信息
     */
    public List<CourtsBean> getCourtsAll() {
        List<CourtsBean> courtsBeanList = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id";
        courtsBeanList = JDBCUtil.list(sql, CourtsBean.class);
        return courtsBeanList;
    }

    /**
     * 添加一个球桌
     */
    public int addCourts(CourtsBean court) {
        //对添加对象类型的名字进行进行处理，得到对应的类型id
        for (CourtsTypeBean courtsType : courtsTypeBeanList) {
            if (courtsType.getCourts_type_name() != null) {
                if (courtsType.getCourts_type_name().equals(court.getCourts_type_name()))
                    court.setCourts_type_id(courtsType.getCourts_type_id());
            }
        }
        court.setCourts_table(court.getCourts_table() + "台号");
        int num = 0;
        String sql = "insert into Courts(courts_table,courts_status_id,courts_type_id) values (?,?,?)";
        num = JDBCUtil.update(sql, court.getCourts_table(), 0, court.getCourts_type_id());
        return num;
    }

    /**
     * 通过台球类型查询信息
     */
    public List<CourtsBean> searchCourtsByStatus(int courts_status_id) {
        List<CourtsBean> courts = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and court_status=?";
        courts = JDBCUtil.list(sql, CourtsBean.class, courts_status_id);
        return courts;
    }

    /**
     * 订台的修改状态
     */
    public int updateStatusBooking(String courts_table, int courts_status_id, Timestamp courts_start_time) {
        int num = 0;
        String sql = "update courts set courts_status_id=?,courts_start_time = '" + courts_start_time + "' where courts_table=?";
        num = JDBCUtil.update(sql, courts_status_id, courts_table);
        return num;
    }

    /**
     * 结算的修改状态
     */
    public int updateStatusSettle(String courts_table, int courts_status_id, Timestamp courts_end_time) {
        int num = 0;
        String sql = "update courts set courts_status_id=?,courts_end_time = '" + courts_end_time + "' where courts_table=?";
        num = JDBCUtil.update(sql, courts_status_id, courts_table);
        return num;
    }

    /**
     * 修改台球
     */
    public int updateCourts(CourtsBean court) {
        //对添加对象类型的名字进行进行处理，得到对应的类型id
        for (CourtsTypeBean courtsType : courtsTypeBeanList) {
            if (courtsType.getCourts_type_name() != null) {
                if (courtsType.getCourts_type_name().equals(court.getCourts_type_name()))
                    court.setCourts_type_id(courtsType.getCourts_type_id());
            }
        }
        if (court.getCourts_status_name() != null) {
            if (court.getCourts_status_name().equals("无人使用"))
                court.setCourts_status_id(0);
            else if (court.getCourts_status_name().equals("有人使用"))
                court.setCourts_status_id(1);
            else if (court.getCourts_status_name().equals("维修中"))
                court.setCourts_status_id(2);
        }
        String sql = "update Courts set courts_table=?,courts_status_id=?,courts_type_id=? where court_id=?";
        return JDBCUtil.update(sql, court.getCourts_table(), court.getCourts_status_id(), court.getCourts_type_id(), court.getCourt_id());
    }

    /**
     * 查询所有台球类型
     */
    public static List<CourtsTypeBean> getCourtsTypeAll() {
        String sql = "select courts_type_id,courts_type_name,courts_type_img,courts_unit_price from courts_type";
        return JDBCUtil.list(sql, CourtsTypeBean.class);
    }

    /**
     * 通过获取球桌名指定球桌信息
     */
    public List<CourtsBean> getCourtsByName(String court_table) {
        //通过名字模糊查询的
        List<CourtsBean> courtsBeanList = null;
        //adw
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_table like '%" + court_table + "%'";
        courtsBeanList = JDBCUtil.list(sql, CourtsBean.class);
        return courtsBeanList;
    }

    /**
     *
     */
    public List<CourtsBean> getCourtsByStatus(String court_status) {
        List<CourtsBean> list = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_status_name = '" + court_status + "'";
        list = JDBCUtil.list(sql, CourtsBean.class);
        return list;
    }

    public List<CourtsBean> getCourtsByType(String court_type) {
        List<CourtsBean> list = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_type_name = '" + court_type + "'";
        list = JDBCUtil.list(sql, CourtsBean.class);
        return list;
    }

    public List<CourtsBean> getCourtsByTypeStaus(String court_status, String court_type) {
        List<CourtsBean> list = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_type_name = '" + court_type + "' and courts_status_name = '" + court_status + "'";
        list = JDBCUtil.list(sql, CourtsBean.class);
        return list;
    }

    public List<CourtsBean> getCourtsByNameStaus(String court_name, String court_status) {
        List<CourtsBean> list = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_table like '%" + court_name + "%' and courts_status_name = '" + court_status + "'";
        list = JDBCUtil.list(sql, CourtsBean.class);
        return list;
    }

    public List<CourtsBean> getCourtsByNameType(String court_name, String court_type) {
        List<CourtsBean> list = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_table like '%" + court_name + "%' and courts_type_name = '" + court_type + "'";
        list = JDBCUtil.list(sql, CourtsBean.class);
        return list;
    }

    public List<CourtsBean> getCourtsByNameTypeStaus(String court_name, String court_type, String court_status) {
        List<CourtsBean> list = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and courts_table like '%" + court_name + "%' and courts_status_name = '" + court_status + "' and courts_type_name = '" + court_type + "'";
        list = JDBCUtil.list(sql, CourtsBean.class);
        return list;
    }

    public CourtsBean getCourtsByCourId(int court_id) {
        CourtsBean courtsBean = null;
        String sql = "select court_id,courts_start_time,courts_end_time,courts_table,Courts.courts_status_id,Courts.courts_type_id,courts_status_name,courts_type_name,courts_type_img,courts_unit_price from Courts,Courts_status,Courts_type where Courts.courts_status_id = Courts_status.courts_status_id and Courts.courts_type_id = Courts_type.courts_type_id and court_id = " + court_id + "";
        courtsBean = JDBCUtil.one(sql, CourtsBean.class);
        return courtsBean;
    }

    public int restoreCourtsByCourId(int courtId){
        int num = 0;
        String sql = "update courts set courts_status_id=0,courts_start_time = null,courts_end_time=null where court_id=?";
        num = JDBCUtil.update(sql,courtId);
        return num;
    }

    public static void main(String[] args) {
        System.out.println(new CourtsDao().getCourtsAll());
    }
}
